using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using DTOLayer;
using System.Data;

namespace DAOLayer
{
    public class LoaiTheDocGiaDAO:AbstractDao
    {
        public int TestExisted(LoaiTheDocGiaDTO dto)
        {
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            int kq = -1;
            try
            {
                cn.Open();
                String str = "select count(*) from LoaiTheDocGia where TenLoaiThe = ?";
                SqlCommand cmd = new SqlCommand(str, cn);
                cmd.Parameters.Add("@TenLoaiThe", SqlDbType.NChar);
                cmd.Parameters["@TenLoaiThe"].Value = dto.TenLoaiThe;
                if ((int)cmd.ExecuteScalar() == 0)
                    kq = 1;
                return kq;
            }
            catch (Exception e)
            {
                return kq;
            }
            finally
            {
                cn.Close();
            }
        }
        public int InsertLoaiTheDocGia(LoaiTheDocGiaDTO dto)
        {
            int kq = TestExisted(dto);
            if (kq == -1)
            {
                return -1; // da ton tai
            }
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            try
            {
                cn.Open();
                String str = "Insert into LoaiTheDocGia (TenLoaiThe,Ghichu) values (?,?)" + "";
                SqlCommand cmd = new SqlCommand(str, cn);                
                cmd.Parameters.Add("@TenLoaiThe", SqlDbType.NChar);
                cmd.Parameters["@TenLoaiThe"].Value = dto.TenLoaiThe;
                cmd.Parameters.Add("@Ghichu", SqlDbType.NChar);
                cmd.Parameters["@Ghichu"].Value = dto.GhiChu;
                cmd.ExecuteNonQuery();
                str = "select @@IDENTITY";
                cmd = new SqlCommand(str, cn);
                dto.MaLoaiThe = (int)cmd.ExecuteScalar();
                return dto.MaLoaiThe;
            }
            catch (Exception e)
            {
                return -1;
            }
            finally
            {
                cn.Close();
            }
        }
        public int DeleteLoaiTheDocGia(int maloaithe)
        {
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            try
            {
                cn.Open();
                String str = "delete from LoaiTheDocGia where MaLoaiThe = ?";
                SqlCommand cmd = new SqlCommand(str, cn);
                cmd.Parameters.Add("@MaLoaiThe", SqlDbType.Int);
                cmd.Parameters["@MaLoaiThe"].Value = maloaithe;
                cmd.ExecuteNonQuery();
                return 1;
            }
            catch (Exception e)
            {
                return -1;
            }
            finally
            {
                cn.Close();
            }

        }
        public int UpdateLoaiTheDocGia(LoaiTheDocGiaDTO dto)
        {
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            try
            {
                cn.Open();
                String str = "Update LoaiTheDocGia set TenLoaiThe = ?, Ghichu = ? where MaLoaiThe = ?";
                SqlCommand cmd = new SqlCommand(str, cn);
                cmd.Parameters.Add("@TenLoaiThe", SqlDbType.NChar);
                cmd.Parameters["@TenLoaiThe"].Value = dto.TenLoaiThe;
                cmd.Parameters.Add("@Ghichu", SqlDbType.NChar);
                cmd.Parameters["@Ghichu"].Value = dto.GhiChu;
                cmd.Parameters.Add("@MaLoaiThe", SqlDbType.NChar);
                cmd.Parameters["@MaLoaiThe"].Value = dto.MaLoaiThe;
                cmd.ExecuteNonQuery();
                return 1;
            }
            catch (Exception e)
            {
                return -1;
            }
            finally
            {
                cn.Close();
            }

        }
        public DataTable LayBang()
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection cn;
                cn = this.SqlConnectiondata();
                cn.Open();
                String str = "select * from LoaiTheDocGia";

                SqlDataAdapter d = new SqlDataAdapter(str, cn);
                d.Fill(dt);
                cn.Close();

            }
            catch (Exception e)
            {
                return null;
            }
            return dt;
        }
    }
}
